﻿
using System.Collections.Generic;
using Entity;
using System.Data.SqlClient;
using DbCommon;
using System.Data;

namespace DAL
{
    public class UserRolesDAL
    {
        DbHelper dbhelper = new DbHelper();
        /// <summary>
        /// 添加一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Insert(BoUserRoles model)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("INSERT INTO [UserRoles]");
            sql.Append("([USE_TEACHERS],[USE_ROLES])");
            sql.Append("VALUES");
            sql.Append("(@USE_TEACHERS,@USE_ROLES)");
            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 修改一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Update(BoUserRoles model)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [UserRoles] SET ");
            sql.Append(" WHERE ");
            sql.Append("USE_ROLES=@USE_ROLES"); sql.Append("USE_TEACHERS=@USE_TEACHERS");

            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <returns></returns>
        public int Delete(string uSE_ROLES, string uSE_TEACHERS)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("DELETE FROM UserRoles");
            sql.Append(" WHERE ");
            sql.Append("USE_ROLES=@USE_ROLES AND USE_TEACHERS=@USE_TEACHERS");
            SqlParameter[] parameter = new SqlParameter[2];
            parameter[0] = new SqlParameter("@USE_ROLES", SqlDbType.NVarChar, 50);
            parameter[1] = new SqlParameter("@USE_TEACHERS", SqlDbType.NVarChar, 50);
            parameter[0].Value = uSE_ROLES;
            parameter[1].Value = uSE_TEACHERS;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }

        /// <summary>
        /// 获取一条记录
        /// </summary>
        /// <returns></returns>
        public int GetModel(string uSE_ROLES, string uSE_TEACHERS)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT USE_TEACHERS,USE_ROLES FROM UserRoles");
            sql.Append(" WHERE ");
            sql.Append("USE_ROLES=@USE_ROLES AND USE_TEACHERS=@USE_TEACHERS");
            SqlParameter[] parameter = new SqlParameter[2];
            parameter[0] = new SqlParameter("@USE_ROLES", SqlDbType.NVarChar, 50);
            parameter[1] = new SqlParameter("@USE_TEACHERS", SqlDbType.NVarChar, 50);
            parameter[0].Value = uSE_ROLES;
            parameter[1].Value = uSE_TEACHERS;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            List<BoUserRoles> list = GetList(table);
            return list != null && list.Count > 0 ? list.Count : 0;
        }

        public IList<BoUserRoles> GetTeacherRoles(string teacherId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT USE_TEACHERS,USE_ROLES FROM UserRoles");
            sql.Append(" WHERE ");
            sql.Append(" USE_TEACHERS=@USE_TEACHERS");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@USE_TEACHERS", SqlDbType.NVarChar, 50);
            parameter[0].Value = teacherId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            List<BoUserRoles> list = GetList(table);
            return list;
        }

        public int ClearTeacherRoles(string teacherId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("DELETE FROM UserRoles");
            sql.Append(" WHERE ");
            sql.Append(" USE_TEACHERS=@USE_TEACHERS");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@USE_TEACHERS", SqlDbType.NVarChar, 50);
            parameter[0].Value = teacherId;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }

        private List<BoUserRoles> GetList(DataTable table)
        {
            List<BoUserRoles> list = new List<BoUserRoles>();
            if (table != null && table.Rows.Count > 0)
            {
                BoUserRoles model = new BoUserRoles();
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    model.USE_TEACHERS = (string)table.Rows[i]["USE_TEACHERS"];
                    model.USE_ROLES = (string)table.Rows[i]["USE_ROLES"];
                    list.Add(model);
                }
            }
            return list;
        }

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        private SqlParameter[] GetParameters(BoUserRoles model)
        {
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@USE_TEACHERS",SqlDbType.NVarChar,50),
                new SqlParameter("@USE_ROLES",SqlDbType.NVarChar,50),
            };
            parameters[0].Value = model.USE_TEACHERS;
            parameters[1].Value = model.USE_ROLES;
            return parameters;
        }
    }
}